IF OBJECT_ID('dbo.RptSubContractMetricSnapShotDetail') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubContractMetricSnapShotDetail
    IF OBJECT_ID('dbo.RptSubContractMetricSnapShotDetail') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubContractMetricSnapShotDetail >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubContractMetricSnapShotDetail >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptSubContractMetricSnapShotDetail
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/18/2008
--
-- Description: Gets Raw data of SubContractMetricSnapshot
--
-- Serves as a drill down to the Report, RptSubContractMetricSnapshot
--        Calls proper detail source proc. 
--
-- Parameters:  
--     @WeekEndDate           
--     @DeliverableStatus     
--     @DetailType  
--     @DueDateNbr       
--     @DeliverableType  
--     @Institution           
--     @Department            
--     @Unit                  
--     @Subcontractor         
--     @SubcontractSpecialist 
--     @DomainUserId          
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptSubContractMetricSnapShotDetail
--                       @WeekEndDate             = '10/12/2008',
--                       @DeliverableStatus       = 'InQueue',
--                       @DetailType              = 'STATUS',
--                       @DueDateNbr              = NULL,      -- If DetailType is "DUEDATE", this should be 30,60 or 90
--                       @DeliverableType         = 'Subcontract Agreement In',
--                       @Institution             = 'BWH',
--                       @Department              = NULL,
--                       @Unit                    = NULL,
--                       @Subcontractor           = NULL,
--                       @SubcontractSpecialist   = NULL,
--                       @DomainUserId            = 1
--                       
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/18/08    KC        Created
-- 10/24/08    KC        Many interations, many changes, just about there.
--                       Added DeliverableType.
-- 12/06/08    KC        Remmoved column ActiveYN from table.
--
-- =======================================================================
CREATE PROCEDURE dbo.RptSubContractMetricSnapShotDetail
     (
     @WeekEndDate                       DATETIME,
     @DeliverableStatus                 VARCHAR(100) = NULL,
     @DetailType                        VARCHAR(10),             -- Status, Or DueDate
     @DeliverableType                   VARCHAR(50),             -- In or Out, actual value
     @DueDateNbr                        INT          = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @Subcontractor                     VARCHAR(200) = NULL,
     @SubcontractSpecialist             VARCHAR(100) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ReturnCode         INT,
               @StartDate          DATETIME,
               @EndDate            DATETIME

     -- Check codes
     IF UPPER(@DetailType) NOT IN ('STATUS','DUEDATE') OR @DetailType IS NULL
     BEGIN
          RAISERROR ('ERROR! @DetailType Parameter should be "Status" or "DueDate", not "%s".',11,1,@DetailType) 
          RETURN -1
     END

     IF UPPER(@DetailType) = 'DUEDATE' AND (@DueDateNbr NOT IN (30,60,90) OR @DueDateNbr IS NULL)
          BEGIN
               RAISERROR ('ERROR! When @DetailType is DUEDATE, @DueDateNbr needs to be 30, 60, or 90, not "%d".',11,1,@DueDateNbr) 
               RETURN -1
     END
     

     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @Subcontractor IN ('ALL','','0') SET @Subcontractor = NULL
     IF @SubcontractSpecialist IN ('ALL','','0') SET @SubcontractSpecialist = NULL
               
     -- Return Result Set
     SELECT    AsOfDate,                
               WeekStartDate,           
               WeekEndDate,             
               AgreementId,              
               FolderNumber,            
               InfoEdPropNumber,         
               ProposalShortTitle,       
               Institution,              
               InstitutionId,            
               Department,               
               DepartmentId,             
               Unit,                     
               UnitId,                   
               ChiefCode,                
               InstrumentType,               
               PrincipalInvestigatorId, 
               PrincipalInvestigator,   
               SponsorID,                
               SponsorName,              
               SubContractorId,         
               SubContractorName,       
               Specialist,              
               ReportId,                
               ReportName,              
               REPORTCAT,                    
               REPORTSTAT,              
               SubmittedDate,           
               DueDate,                 
               ProcessedDate,           
               CompletedDate,           
               SubmittedBy,             
               StatusBy,                
               CompletedBy,             
               PeriodStartDate,         
               PeriodEndDate,           
               BudgetStartDate,         
               BudgetEndDate,           
               FundNumber,              
               REPORTCAT_CODE,               
               REPORTSTAT_CODE          
     FROM      dbo.SubContractDeliverableSnapShot S
     WHERE     (S.REPORTSTAT = @DeliverableStatus) AND
               
               -- Which type of Record?
               ((UPPER(@DetailType) = 'STATUS' AND S.WeekEndDate = @WeekEndDate) OR
               (UPPER(@DetailType) = 'DUEDATE' AND S.WeekEndDate = @WeekEndDate AND S.DueDate BETWEEN @WeekEndDate AND DATEADD(DD,@DueDateNbr,@WeekEndDate))) AND
               
               (S.REPORTCAT = @DeliverableType) AND
               
               (@Institution IS NULL OR S.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR S.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR S.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@Subcontractor IS NULL OR S.SubContractorName = @Subcontractor) AND
               (@SubcontractSpecialist IS NULL OR S.Specialist = @SubcontractSpecialist)

     -- check for errors
     SELECT @Error=@@ERROR
     IF @Error != 0 OR @ReturnCode != 0
     BEGIN
          RETURN -1
     END


     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptSubContractMetricSnapShotDetail') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubContractMetricSnapShotDetail >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubContractMetricSnapShotDetail >>>'
go

GRANT EXECUTE ON dbo.RptSubContractMetricSnapShotDetail TO InsightReport,Insight
go


